package com.adopapa.importexcel;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Importer {

	/**
	 * 读入execl文档
	 */
	public static Workbook getExcelFile(File file) throws Exception {
		BufferedInputStream in = null;
		try {
			in = new BufferedInputStream(new FileInputStream(file));
			String fileName = file.getName();
			Workbook workbook = null;
			if (fileName.endsWith(".xlsx")) {
				workbook = new XSSFWorkbook(in);
			} else if (fileName.endsWith(".xls")) {
				workbook = new HSSFWorkbook(in);
			} else {
				throw new RuntimeException("不是EXCEL文件");
			}
			return workbook;
		} finally {
			IOUtils.closeQuietly(in);
		}

	}

	public static Workbook getExcelFile(InputStream inputStream, String extName) throws IOException {
		BufferedInputStream in = new BufferedInputStream(inputStream);
		Workbook wb = null;
		if (".xlsx".equalsIgnoreCase(extName)) {
			wb = new XSSFWorkbook(in);
		} else if (".xls".equalsIgnoreCase(extName)) {
			wb = new HSSFWorkbook(in);
		} else {
			throw new RuntimeException("不是EXCEL文件");
		}
		return wb;
	}

	public static SheetContent praseSheet(Sheet sheet) {
		int contentCount = sheet.getPhysicalNumberOfRows();
		Row fieldNameRow = sheet.getRow(0);
		int fieldCout = fieldNameRow.getPhysicalNumberOfCells();
		SheetContent sheetContent = new SheetContent(fieldCout, contentCount);
		Cell cell = null;

		for (int i = 0; i < fieldCout; i++) {
			cell = fieldNameRow.getCell(i);
			sheetContent.addFieldName(i, cell == null ? "" : cell.getStringCellValue());
		}

		Row titleRow = sheet.getRow(1);
		for (int i = 0; i < fieldCout; i++) {
			cell = titleRow.getCell(i);
			sheetContent.addTitle(i, cell == null ? "" : cell.getStringCellValue());
		}

		Row contentRow = null;
		for (int i = 2; i < contentCount; i++) {
			contentRow = sheet.getRow(i);
			for (int j = 0; j < fieldCout; j++) {
				cell = contentRow.getCell(j);
				sheetContent.addFieldValue(j, i, getCellValue(cell));
			}
		}
		return sheetContent;

	}

	public static Object getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		int cellType = cell.getCellType();
		switch (cellType) {
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}
			return cell.getNumericCellValue();
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_FORMULA:
			try {
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					return cell.getDateCellValue();
				}
				return cell.getNumericCellValue();
			} catch (Exception e) {
				return cell.getStringCellValue();
			}
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();
		default:
			return null;
		}// end switch
	}

	public static List<String> validateSheet(SheetContent sheetContent, Class<?> clazz) {
		List<String> messages = new LinkedList<>();
		String[] fieldNames = sheetContent.getFieldNames();
		String[] titles = sheetContent.getTitles();
		Map<String, Field> fieldMap = getExcelFieldMap(clazz);

		// 校验字段个数是否一致
		if (fieldNames.length != fieldMap.size()) {
			messages.add("Excel文档字段个数" + fieldNames.length + "与对象字段个数" + fieldMap.size() + "不一致");
			return messages;
		}
		// 校验字段名称是否一致
		boolean flag = true;
		Field field = null;
		for (int i = 0; i < fieldNames.length; i++) {
			field = fieldMap.get(fieldNames[i]);
			if (field == null) {
				messages.add("Excel文档字段名称" + titles[i] + "字段名" + fieldNames[i] + "与对象字段名" + fieldMap.keySet());
				flag = false;
			}
			if (field != null) {
				Unique unique = field.getAnnotation(Unique.class);
				if (unique != null) {
					sheetContent.addUnique(field.getName());
				}
			}
		}
		if (!flag) {
			return messages;
		}

		// 校验字段值类型是否符合要求
		Object value = null;
		int contentCount = sheetContent.getContentCount();
		for (int i = 0; i < fieldNames.length; i++) {
			for (int j = 2; j < contentCount; j++) {
				value = sheetContent.getFieldValue(i, j);
				field = fieldMap.get(fieldNames[i]);
				if (value != null && value.getClass() != field.getType()) {
					if (value.getClass() == Double.class //
							&& (field.getType() == Integer.class || field.getType() == Long.class)) {
						// 从Excel中读到的整数为小数形式
						continue;
					}
					if (value.getClass() == String.class //
							&& (field.getType() == Integer.class || field.getType() == Long.class)) {
						try {
							Integer.parseInt(value.toString());
							continue;
						} catch (Exception e) {
							
						}
					}
					StringBuilder sb = new StringBuilder();
					sb.append("第").append(j + 1).append("行第").append(i + 1).append("列字段值").append(value);
					sb.append("类型").append(value.getClass()).append("与对象类型").append(field.getType()).append("不一致");
					messages.add(sb.toString());
					flag = false;
				}
			}
		}
		if (!flag) {
			return messages;
		}

		// 校验唯一性字段
		if (sheetContent.getUniqueFields().size() > 0) {
			for (String fieldName : sheetContent.getUniqueFields()) {
				Object[] fieldValues = sheetContent.getFieldValues(fieldName);
				Map<Object, Object> map = new HashMap<Object, Object>();
				for (int i = 2; i < fieldValues.length; i++) {
					if (fieldValues[i] == null) {
						messages.add("第" + i + "行" + fieldName + "列存在空值");
					} else if (!map.containsKey(fieldValues[i])) {
						map.put(fieldValues[i], fieldValues[i]);
					} else {
						messages.add("第" + i + "行" + fieldName + "列字段值" + fieldValues[i] + "存在重复值");
					}
				}
			}
		}

		return messages;
	}

	public static <T> List<T> buildObjects(SheetContent sheetContent, Class<T> clazz) throws Exception {
		List<T> instances = new LinkedList<>();
		String[] fieldNames = sheetContent.getFieldNames();
		Map<String, Field> fieldMap = getExcelFieldMap(clazz);

		// 构建Method
		Method[] methods = new Method[fieldNames.length];
		for (int i = 0; i < methods.length; i++) {
			String methodName = "set"
					.concat(fieldNames[i].substring(0, 1).toUpperCase().concat(fieldNames[i].substring(1)));
			methods[i] = clazz.getDeclaredMethod(methodName, fieldMap.get(fieldNames[i]).getType());
		}

		T instance = null;
		Object value = null;
		int contentCount = sheetContent.getContentCount();
		for (int i = 2; i < contentCount; i++) {
			instance = clazz.newInstance();
			for (int j = 0; j < fieldNames.length; j++) {
				value = sheetContent.getFieldValue(j, i);
				if (value.getClass() == String.class) {
					if (fieldMap.get(fieldNames[j]).getType() == Integer.class) {
						Double dd = Double.parseDouble((String) value);
						value = dd.intValue();
					}
					if (fieldMap.get(fieldNames[j]).getType() == Long.class) {
						Double dd = Double.parseDouble((String) value);
						value = dd.longValue();
					}
				} else if (value.getClass() == Double.class) {
					if (fieldMap.get(fieldNames[j]).getType() == Integer.class) {
						Double dd = (Double) value;
						value = dd.intValue();
					}
					if (fieldMap.get(fieldNames[j]).getType() == Long.class) {
						Double dd = (Double) value;
						value = dd.longValue();
					}
				}
//				if (fieldMap.get(fieldNames[j]).getType() == Integer.class && value.getClass() == Double.class) {
//					Double dd = (Double) value;
//					value = dd.intValue();
//				}
//				if (fieldMap.get(fieldNames[j]).getType() == Long.class && value.getClass() == Double.class) {
//					Double dd = (Double) value;
//					value = dd.longValue();
//				}
				methods[j].invoke(instance, value);
			}
			instances.add(instance);
		}
		return instances;
	}

	/*
	 * public static Field[] getExcelFields(Class clazz) { List<Field>
	 * filteredFields = new LinkedList<>(); Field[] fields =
	 * clazz.getDeclaredFields(); for (Field field : fields) { ExcelField excelField
	 * = field.getAnnotation(ExcelField.class); if (excelField != null) {
	 * filteredFields.add(field); } } Field[] results = new
	 * Field[filteredFields.size()]; int count = 0; for (Field field :
	 * filteredFields) { results[count] = field; count++; } return results; }
	 */

	public static Map<String, Field> getExcelFieldMap(Class<?> clazz) {
		Map<String, Field> results = new HashMap<String, Field>();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			ExcelField excelField = field.getAnnotation(ExcelField.class);
			if (excelField != null) {
				results.put(field.getName(), field);
			}
		}
		return results;
	}

}
